﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using Next.CodeGen.Core.DataSetUtility.SQLSchemaTableAdapters;
using Next.CodeGen.Core.DataSetUtility;
using System.IO;

namespace Next.CodeGen.Core.Generators
{
    public class Schema
    {
        public Schema()
        {

        }

        public static void GenerateSchema()
        {
            int CountTables = 0;
            Console.WriteLine("Schema generation started.");
            string Schema = "";
            using (SQLSchema.tablesDataTable TableDT = new SQLSchema.tablesDataTable())
            {
                using (tablesTableAdapter TableDA = new tablesTableAdapter())
                {
                    //INST : Get All Tables
                    TableDA.Fill(TableDT);
                    #region Looping on tables
                    CountTables = TableDT.Count;
                    Schema += "<Tables>\r\n";
                    foreach (SQLSchema.tablesRow TableItem in TableDT.Rows)
                    {
                        Schema += "\t<Table name='" + TableItem.name + "'>\r\n";
                        using (SQLSchema.columnsDataTable ColumnDT = new SQLSchema.columnsDataTable())
                        {
                            using (columnsTableAdapter ColumnDA = new columnsTableAdapter())
                            {
                                //INST : Get All Columns For The Specified Table
                                ColumnDA.FillByTableID(ColumnDT, TableItem.object_id);
                                #region Looping on columns
                                Schema += "\t\t<Columns>\r\n";
                                foreach (SQLSchema.columnsRow ColumnItem in ColumnDT)
                                {
                                    Schema += "\t\t\t<Column ";
                                    Schema += "name='" + ColumnItem.name + "' ";
                                    #region Retrieving ColumnType
                                    using (SQLSchema.typesDataTable TypeDT = new SQLSchema.typesDataTable())
                                    {
                                        using (typesTableAdapter TypeDA = new typesTableAdapter())
                                        {
                                            TypeDA.FillByTypeID(TypeDT, ColumnItem.user_type_id);
                                            Schema += "type='" + TypeDT.FirstOrDefault().name + "' ";
                                        }
                                    }
                                    #endregion
                                    //Schema += "maximumlength='" + ColumnItem.ItemArray[8] + "' ";
                                    Schema += "is_required='" + !ColumnItem.is_nullable + "' ";
                                    if (ColumnItem.is_identity)
                                    {
                                        Schema += "is_identity='" + ColumnItem.is_identity + "' ";
                                    }
                                    Schema += "/>\r\n";
                                }
                                Schema += "\t\t</Columns>\r\n";
                                #endregion
                            }
                        }
                        using (SQLSchema.relationsDataTable RelationDT = new SQLSchema.relationsDataTable())
                        {
                            using (relationsTableAdapter RelationDA = new relationsTableAdapter())
                            {
                                //INST : Get All Relations For The Specified Table
                                RelationDA.FillByTableID(RelationDT, TableItem.object_id);
                                #region Looping on relations
                                if (RelationDT.Count > 0)
                                {
                                    Schema += "\t\t<Relations>\r\n";
                                    foreach (SQLSchema.relationsRow RelationItem in RelationDT)
                                    {
                                        Schema += "\t\t\t<Relation ";
                                        Schema += "name='" + RelationItem.foreign_key_name + "' ";
                                        Schema += "localtable='" + RelationItem.table_name + "' ";
                                        Schema += "localcolumn='" + RelationItem.constraint_column_name + "' ";
                                        Schema += "referencetable='" + RelationItem.referenced_object + "' ";
                                        Schema += "referencecolumn='" + RelationItem.referenced_column_name + "' ";
                                        Schema += "deleteaction='" + RelationItem.delete_referential_action_desc + "' ";
                                        Schema += "updateaction='" + RelationItem.update_referential_action_desc + "' ";
                                        Schema += "/>\r\n";
                                    }
                                    Schema += "\t\t</Relations>\r\n";
                                }
                                #endregion
                            }
                        }
                        Schema += "\t</Table>\r\n";
                    }
                    Schema += "</Tables>";
                    #endregion
                }
            }
            //INST : Save Result To File in 
            SaveSchema(Schema);
            Console.WriteLine(CountTables + " Tables generated successfully!");
        }

        /// <summary>
        /// Save the schema to Schema.xml file under SchemaPath.
        /// </summary>
        /// <param name="Schema">The content of the Schema.xml file.</param>
        private static void SaveSchema(string Schema)
        {
            //INST : Create Schema Folder if not exist
            string SchemaFolder = Properties.Settings.Default.CommonControlsProjectPath + "\\Schema";
            Directory.CreateDirectory(SchemaFolder);

            //INST : Write Schema To Specified File
            string SchemaFileName = "";
            SchemaFileName = "\\Schema.xml";
            using (StreamWriter File = new StreamWriter(@SchemaFolder + SchemaFileName))
            {
                File.WriteLine(Schema);
            }
        }
    }
}
